<!doctype html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width">
        <meta name="restype" content="documentation">
        <meta name="product" content="kendo-ui">
        <meta name="subproduct" content="spreadsheet">
        <title>Cell Formatting | Kendo UI Spreadsheet</title>
        <link rel="shortcut icon" href="/favicon.ico"/>
        <meta name="description" content="Learn how to format cells in the Kendo UI Spreadsheet widget.">
        <link href="//fonts.googleapis.com/css?family=Roboto:400,500,700,900" rel="stylesheet">
        <link href='https://fonts.googleapis.com/css?family=Roboto+Mono:400,500,700' rel='stylesheet' type='text/css'>
        <!-- <link href="https://kendo.cdn.telerik.com/2017.3.1026/styles/kendo.common.min.css" rel="stylesheet" /> -->
        <link rel="stylesheet" href="http://oygy5legq.bkt.clouddn.com/kendo.common.min.css">
        <link rel="stylesheet" href="/assets/styles.css?cb=8338967476da1c756a6defe19c1f9984">
        <link rel="stylesheet" href="/assets/icon-font.css?cb=25d8b9593057297c278fcd6d7d263c8e">
        <link rel="stylesheet" href="/assets/theme.css?cb=d82da02940126146560da1ae3bb0bf64">
        
        <!-- <script src="https://code.jquery.com/jquery-1.10.2.js"></script> -->
        <script src="https://cdn.bootcss.com/jquery/1.10.2/jquery.min.js"></script>
        <!-- <script src="https://kendo.cdn.telerik.com/2017.3.1026/js/kendo.web.min.js"></script> -->
        <script src="http://oygy5legq.bkt.clouddn.com/kendo.web.min.js"></script>
        <!-- <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.6.0/codemirror.min.css">
        <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.6.0/codemirror.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.6.0/mode/javascript/javascript.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.6.0/mode/css/css.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.6.0/mode/xml/xml.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.6.0/mode/htmlmixed/htmlmixed.min.js"></script> -->
        <script src="/assets/prettify.js?cb=9671553084e1962263b152af1f563c3c"></script>
        <script src="/assets/app.js?cb=c577f29da14c3752a8c4ba001dde39e5"></script>
        
        

        <!--[if lt IE 9]>
        <script>
        document.createElement('header');
        document.createElement('nav');
        document.createElement('section');
        document.createElement('article');
        document.createElement('aside');
        document.createElement('footer');
        document.createElement('hgroup');
        </script>
        <![endif]-->
    </head>
    <body>
        <!-- Google Tag Manager -->
        <!-- <noscript>
            <iframe src="//www.googletagmanager.com/ns.html?id=GTM-6X92" height="0" width="0" style="display: none; visibility: hidden"></iframe>
        </noscript>
        <script>(function(w, d, s, l, i) { w[l] = w[l] || []; w[l].push({ 'gtm.start': new Date().getTime(), event: 'gtm.js' }); var f = d.getElementsByTagName(s)[0], j = d.createElement(s), dl = l != 'dataLayer' ? '&l=' + l : ''; j.async = true; j.src = '//www.googletagmanager.com/gtm.js?id=' + i + dl; f.parentNode.insertBefore(j, f); })(window, document, 'script', 'dataLayer', 'GTM-6X92');</script> -->
        <!-- End Google Tag Manager -->
        <aside class="TK-Hat">
    <figure class="TK-Hat-Brand">
        <a href="https://www.progress.com" class="TK-PRGS-Logo">
            <svg xmlns="http://www.w3.org/2000/svg" width="94" height="40" viewBox="0 0 512 120">
                <path fill="#5ce500" d="M95.52 29.33v51a3.93 3.93 0 0 1-1.78 3.08l-1.67 1-12.72 7.35-8.59 5-1.78 1V42.6L21.23 15 43.91 1.93 46 .74a3.94 3.94 0 0 1 3.56 0L81 18.9l14.51 8.38v2.05zM58.36 48.72l-9.79-5.66-22.91-13.23a4 4 0 0 0-3.56 0L1.77 41.57 0 42.6l34.49 19.91v39.83l20.3-11.73 1.79-1a3.94 3.94 0 0 0 1.78-3.08V48.72zM0 82.43l23.86 13.78V68.63z"></path>
                <path fill="#4b4e52" d="M148.09 27.28h-26v70.48h11.55V70.1h14.57c15.77 0 24.45-7.7 24.45-21.69 0-6.35-2.4-21.12-24.55-21.12m12.78 21.31c0 7.95-4.12 11.19-14.24 11.19h-13v-22.1h14.57c8.56 0 12.71 3.57 12.71 10.91M207 46.41l.87.42-2 10.42-1.35-.42a11.32 11.32 0 0 0-3.34-.51c-10.79 0-11.67 8.59-11.67 19v22.44h-10.64V46h10v6.24c2.73-4.2 6-6.37 10.37-6.9a14.55 14.55 0 0 1 7.76 1.07M233.29 45c-8.42 0-15.16 3.2-19.5 9.27-4.56 6.37-5.23 13.85-5.23 17.74 0 16.36 9.7 26.92 24.73 26.92 18.26 0 24.73-14.71 24.73-27.3 0-7.25-2.15-13.82-6-18.51-4.41-5.31-10.87-8.12-18.7-8.12m0 44.38c-8.37 0-13.57-6.66-13.57-17.37s5.2-17.55 13.57-17.55S247 61.23 247 71.78c0 10.83-5.24 17.56-13.66 17.56m114.55-42.93l.87.42-2 10.42-1.35-.42a11.26 11.26 0 0 0-3.33-.51c-10.78 0-11.66 8.59-11.66 19v22.44h-10.66V46h10v6.24c2.73-4.2 6-6.37 10.37-6.9a14.54 14.54 0 0 1 7.73 1.06m38.4 34.76l-.2.57c-2.23 6.36-7.57 7.7-11.65 7.7-8.09 0-13.3-5.37-13.81-14.09h36.59l.13-1a31.26 31.26 0 0 0 .12-4.12v-.93C396.93 54.78 387.48 45 374 45c-7.9 0-14.37 3.1-18.73 9a30.85 30.85 0 0 0-5.54 18c0 16 9.95 26.74 24.74 26.74 11.45 0 19.33-5.82 22.2-16.38l.33-1.2h-10.7zM361 66.05c.9-7.17 5.81-11.73 12.79-11.73 5.33 0 11.64 3.1 12.52 11.73H361zm-60.7-15.71c-3.45-3.58-8.06-5.39-13.76-5.39-15.69 0-22.83 13.81-22.83 26.63 0 13.16 7.06 26.44 22.83 26.44a18.33 18.33 0 0 0 13.35-5.42c0 2.28-.1 4.45-.16 5.38-.58 8.54-4.68 12.51-12.91 12.51-4.47 0-9.61-1.59-10.6-6l-.22-1h-10.54l.17 1.41c1.1 9.12 9.11 14.79 20.9 14.79 10.34 0 17.7-3.9 21.28-11.26 1.73-3.55 2.6-8.72 2.6-15.37V46h-10.13v4.34zm-13.11 38.15c-3.74 0-12.43-1.69-12.43-17.37 0-10.3 4.87-16.7 12.71-16.7 6.06 0 12.52 4.39 12.52 16.7 0 10.87-4.79 17.37-12.81 17.37m159.67-6.31c0 8.23-6.83 16.53-22.09 16.53-13.5 0-21.53-5.85-22.61-16.45l-.15-1.1h10.52l.21.84c1.29 6.38 7.37 7.72 12.24 7.72 5.34 0 11-1.72 11-6.54 0-2.44-1.59-4.18-4.73-5.16-1.86-.55-4.15-1.2-6.56-1.87-4.16-1.16-8.47-2.38-11.12-3.29-6.56-2.35-10.33-6.93-10.33-12.56 0-10.43 10.16-15.11 20.22-15.11 13.46 0 20.42 5.07 21.3 15.49l.09 1.07H434.5l-.14-.82c-1-6-7-6.9-10.48-6.9-3 0-10 .53-10 5.5 0 2.25 1.93 3.91 5.89 5.06 1.18.33 2.94.78 5 1.31 4.22 1.09 9.48 2.46 12.13 3.37 6.59 2.32 9.93 6.67 9.93 13m49.39 0c0 8.23-6.83 16.53-22.09 16.53-13.5 0-21.53-5.85-22.61-16.45l-.11-1.09H462l.12.74c1.29 6.38 7.37 7.72 12.24 7.72 5.34 0 11-1.72 11-6.54 0-2.44-1.59-4.18-4.72-5.16-1.86-.55-4.15-1.2-6.57-1.87-4.16-1.16-8.46-2.38-11.11-3.29-6.57-2.35-10.33-6.93-10.33-12.56 0-10.43 10.16-15.11 20.22-15.11 13.46 0 20.42 5.07 21.29 15.49l.09 1.07H483.9l-.14-.82c-1-6-7-6.9-10.48-6.9-3 0-9.95.53-9.95 5.5 0 2.25 1.93 3.91 5.89 5.06 1.18.33 2.94.78 5 1.31 4.22 1.09 9.48 2.46 12.13 3.37 6.58 2.32 9.93 6.67 9.93 13m8.43-30.78a7.37 7.37 0 1 1 7.29-7.37 7.23 7.23 0 0 1-7.29 7.37m0-13.49a6.12 6.12 0 1 0 6 6.12 5.91 5.91 0 0 0-6-6.12m-.85 7.49v2.46h-2.17v-7.74h3.62a2.58 2.58 0 0 1 2.86 2.7 2.26 2.26 0 0 1-1.49 2.34l1.77 2.7H506l-1.49-2.46h-.68zm1.21-3.49h-1.21v1.73h1.21a.86.86 0 0 0 1-.85.88.88 0 0 0-1-.89"></path>
            </svg>
        </a>
    </figure>
</aside>
<header id="page-header">
    <div class="toggle-nav"><span class="k-icon k-i-menu"></span></div>


    <div id="logo-bar">
        <a href="/introduction">
            <img alt="Kendo UI logo" class="logo" src="/images/kendo-ui.svg">
        </a>
    </div>

    
    <span class="show-search">
        <span class="k-icon k-i-search"></span>
    </span>
    
    <div class="nav-buttons">
        <a href="http://demos.telerik.com/kendo-ui/" class="btn demos-btn">Demos</a>
        <a href="http://www.telerik.com/kendo-ui" class="btn about-btn">About</a>
        <a href="http://www.telerik.com/purchase/kendo-ui" class="btn pricing-btn">Pricing</a>
        <a href="http://www.telerik.com/download/kendo-ui" class="btn btn-primary">Try now</a>
    </div>
</header>
        <div id="page-inner-content">
    <div id="page-nav">
        <div id="page-tree"></div>
        <script>
        $("#page-tree").kendoTreeView({
            dataSource: {
                transport: {
                    read: {
                        url: "/default.json",
                        dataType: "json"
                    }
                },
                schema: {
                    model: {
                        id: "path",
                        children: "items",
                        hasChildren: "items"
                    }
                }
            },
            messages: {
                loading: " "
            },
            select: preventParentSelection,
            template: navigationTemplate("/"),
            dataBound: expandNavigation("controls/data-management/spreadsheet/format.html")
        });
        </script>
    </div>

    <a id="page-edit-link" href="https://github.com/telerik/kendo-ui-core/edit/master/docs//controls/data-management/spreadsheet/format.md"><span class="k-icon k-i-pencil"></span> Edit this page</a>
    

    <div id="page-article">
        <article>
            
             <h1 id="cell-formatting"><a href="#cell-formatting">Cell Formatting</a></h1>

<p>The Spreadsheet widget supports cell-formatting options such as formatting of strings, text, numbers, dates, and time.</p>

<h2 id="formatting"><a href="#formatting">Formatting</a></h2>

<h3 id="strings"><a href="#strings">Strings</a></h3>

<p>While the format strings are compatible with the <a href="https://support.office.com/en-au/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4">Excel number formats</a>, some <a href="#differences-to-excel">notable exceptions</a> still exist.</p>

<p>A format string consists of one or more sections that are separated by semicolons. Optionally, a section specifies a color and a condition.</p>

<p>The following example demonstrates how to display a number with up to three decimals.</p>

<h6>Example</h6>

<pre><code>#.###
</code></pre>

<p>The following example demonstrates how to display positive numbers, or zero, in green, and negative numbers in red.</p>

<h6>Example</h6>

<pre><code>[Green]#.###;[Red]#.###
</code></pre>

<p>The following example demonstrates how to display positive numbers in green, negative numbers in red, and the <code>"Zero"</code> text in blue if the number is zero.</p>

<h6>Example</h6>

<pre><code>[Green]#.###;[Red]#.###;[Blue]"Zero"
</code></pre>

<p>This following example is the same as the previous one, with the difference to display any possible text in the cell in magenta.</p>

<h6>Example</h6>

<pre><code>[Green]#.###;[Red]#.###;[Blue]"Zero";[Magenta]@
</code></pre>

<p>The following example demonstrates how to format the Spreadsheet conditionally.</p>

<h6>Example</h6>

<pre data-lang="html"><code>    &lt;script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"&gt;&lt;/script&gt;

    &lt;div id="example"&gt;
    &lt;div id="spreadsheet" style="width: 100%;"&gt;&lt;/div&gt;
    &lt;script&gt;
    $(function() {
        $("#spreadsheet").kendoSpreadsheet({
            excel: {                
                // Required to enable saving files in older browsers
                proxyURL: "//demos.telerik.com/kendo-ui/service/export"
            },
            pdf: {                
                proxyURL: "//demos.telerik.com/kendo-ui/service/export"
            },
            sheets: [
                {
                    name: "Food Order",
                    mergedCells: [
                        "A1:G1",
                        "C15:E15"
                    ],
                    rows: [
                        {
                            height: 70,
                            cells: [
                                {
                                    index: 0, value: "Invoice #52 - 06/23/2015", fontSize: 32, background: "rgb(96,181,255)",
                                    textAlign: "center", color: "white"
                                }
                            ]
                        },
                        {
                            height: 25,
                            cells: [
                                {
                                    value: "ID", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Product", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Quantity", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Price", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Tax", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Amount", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(167,214,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 216321, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Calzone", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 1, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 12.39, format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C3*D3*0.2", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C3*D3+E3", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 546897, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Margarita", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 2, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 8.79, format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C4*D4*0.2", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C4*D4+E4", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 456231, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Pollo Formaggio", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 1, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 13.99, format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C5*D5*0.2", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C5*D5+E5", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 455873, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Greek Salad", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 1, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 9.49, format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C6*D6*0.2", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C6*D6+E6", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 456892, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Spinach and Blue Cheese", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 3, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 11.49, format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C7*D7*0.2", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C7*D7+E7", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 546564, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Rigoletto", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 1, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 10.99, format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C8*D8*0.2", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C8*D8+E8", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 789455, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Creme Brulee", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 5, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 6.99, format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C9*D9*0.2", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C9*D9+E9", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 123002, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Radeberger Beer", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 4, textAlign: "center", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 4.99, format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C10*D10*0.2", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C10*D10+E10", format: "$#,##0.00", background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            cells: [
                                {
                                    value: 564896, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Budweiser Beer", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 3, textAlign: "center", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: 4.49, format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C11*D11*0.2", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    formula: "C11*D11+E11", format: "$#,##0.00", background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            index: 11,
                            cells: [
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(229,243,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            index: 12,
                            cells: [
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(255,255,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            height: 25,
                            index: 13,
                            cells: [
                                {
                                    background: "rgb(193,226,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(193,226,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(193,226,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    background: "rgb(193,226,255)", color: "rgb(0,62,117)"
                                },
                                {
                                    value: "Tip:", background: "rgb(193,226,255)", color: "rgb(0,62,117)", textAlign: "right", verticalAlign: "bottom"
                                },
                                {
                                    formula: "SUM(F3:F11)*0.1", background: "rgb(193,226,255)", color: "rgb(0,62,117)", format: "$#,##0.00", bold: "true", verticalAlign: "bottom"
                                },
                                {
                                    background: "rgb(193,226,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        },
                        {
                            height: 50,
                            index: 14,
                            cells: [
                                {
                                    index: 0, background: "rgb(193,226,255)", color: "rgb(0,62,117)",
                                },
                                {
                                    index: 1, background: "rgb(193,226,255)", color: "rgb(0,62,117)",
                                },
                                {
                                    index: 2, fontSize: 20, value: "Total Amount:",
                                    background: "rgb(193,226,255)", color: "rgb(0,62,117)", textAlign: "right"
                                },
                                {
                                    index: 5, fontSize: 20, formula: "SUM(F3:F14)", background: "rgb(193,226,255)", color: "rgb(0,62,117)",
                                    format: "$#,##0.00", bold: "true"
                                },
                                {
                                    index: 6, background: "rgb(193,226,255)", color: "rgb(0,62,117)"
                                }
                            ]
                        }
                    ],
                    columns: [
                        {
                            width: 100
                        },
                        {
                            width: 215
                        },
                        {
                            width: 115
                        },
                        {
                            width: 115
                        },
                        {
                            width: 115
                        },
                        {
                            width: 155
                        }
                    ]
                }
            ]
        }).data('kendoSpreadsheet').sheets()[0].range("C3:C11").format('[=1][GREEN]#,##0;[=2][YELLOW]#,##0;[=3][CYAN]#,##0;[RED]#,###');
    });
    &lt;/script&gt;
&lt;/div&gt;
</code></pre>

<p>According to the Excel documentation, Excel supports a maximum of four sections. If all of them are present, Excel interprets them in the following order:</p>

<ul>
<li>Positive numbers format</li>
<li>Negative numbers format</li>
<li>Format for zero</li>
<li>Format for text</li>
</ul>

<p>Excel also supports a more flexible conditional formatting.</p>

<p>The following example demonstrates how to display numbers greater than 100 in green, numbers less than minus 100 in yellow, and other numbers in cyan.</p>

<h6>Example</h6>

<pre><code>[&gt;100][GREEN]#,##0;[&lt;=-100][YELLOW]#,##0;[CYAN]#,##0
</code></pre>

<p>In this case, it is not clear whether only up to four sections are allowed, of which the last one must be text, while the Spreadsheet formatter allows for any number of conditional sections.</p>

<h3 id="default-format-strings"><a href="#default-format-strings">Default Format Strings</a></h3>

<p>The default format strings that are shown in the formatting drop-down are stored in the <code>kendo.spreadsheet.formats</code> object.</p>

<table>
<thead>
<tr>
<th>FORMAT IDENTIFIER</th>
<th>VALUE</th>
</tr>
</thead>
<tbody>
<tr>
<td>automatic</td>
<td>Infer formatting by parsing the cell value.</td>
</tr>
<tr>
<td>number</td>
<td>Format numbers with the precision of 2 decimals.</td>
</tr>
<tr>
<td>percent</td>
<td>Format percentage points with the precision of 2 decimals.</td>
</tr>
<tr>
<td>financial</td>
<td>Format financial values that account for positive, negative, or zero values.</td>
</tr>
<tr>
<td>currency</td>
<td>Format currency values that account for positive or negative values.</td>
</tr>
<tr>
<td>date</td>
<td>Format values as dates.</td>
</tr>
<tr>
<td>time</td>
<td>Format values as time.</td>
</tr>
<tr>
<td>dateTime</td>
<td>Format values as date-time.</td>
</tr>
<tr>
<td>duration</td>
<td>Format as an elapsed duration in hours, minutes, or seconds.</td>
</tr>
</tbody>
</table>

<p>You can use these formats with the <a href="/api/javascript/spreadsheet/range#methods-format"><code>range</code> value method</a> in the following way: <code>sheet.range("A1").format(kendo.spreadsheet.formats.currency)</code>.</p>

<h3 id="text-and-numbers"><a href="#text-and-numbers">Text and Numbers</a></h3>

<table>
<thead>
<tr>
<th>CHARACTER</th>
<th>MEANING</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>0</code></td>
<td>Digit placeholder. Displays insignificant zeroes. For example, <code>8.9</code> with the <code>00.000</code> format renders <code>08.900</code>.</td>
</tr>
<tr>
<td><code>#</code></td>
<td>Digit placeholder. Does not display insignificant zeroes. For example, <code>12.34</code> in the <code>###.###</code> format renders <code>12.34</code>.</td>
</tr>
<tr>
<td><code>?</code></td>
<td>Digit placeholder. Functionally similar to <code>0</code>, but displays a space character instead of a zero. To align numbers by decimal points, use this character. Note that you have to use a fixed-width font for this setting to be effective.</td>
</tr>
<tr>
<td><code>.</code></td>
<td>Displays a decimal point.</td>
</tr>
<tr>
<td><code>,</code></td>
<td>Displays a thousands separator or scale (see below).</td>
</tr>
<tr>
<td><code>\</code></td>
<td>Escapes the next character (display literally).</td>
</tr>
<tr>
<td><code>_</code></td>
<td>Skips the width of the next character.</td>
</tr>
<tr>
<td><code>"text"</code></td>
<td>Includes a piece of text in the format. Characters inside are not interpreted in any way, but are literally output.</td>
</tr>
<tr>
<td><code>@</code></td>
<td>Text placeholder. Is replaced with the text in the cell.</td>
</tr>
</tbody>
</table>

<p>The thousands separator (<code>,</code>) has a double role:</p>

<ul>
<li>When situated between any digit placeholders, it outputs a number in thousands that are separated by the separator in the current culture. For example, <code>#,#</code> formats <code>1234567</code> as <code>1,234,567</code>.</li>
<li>When a comma follows a digit placeholder but is not followed by one, it scales the number by one thousand. For example, <code>#.##,</code> formats <code>12345</code> as <code>12.35</code>. This is a more complicated format displaying in which cases such behavior is useful: <code>[&gt;1000000]#.##,,"M";[&gt;1000]#.##,"K";[&gt;0]#"B";[=0]"Empty";[&lt;0]"Replace HDD!"</code>.</li>
</ul>

<h6>Example</h6>

<table>
<thead>
<tr>
<th>VALUE</th>
<th>DISPLAY</th>
</tr>
</thead>
<tbody>
<tr>
<td>12345678</td>
<td>12.35M</td>
</tr>
<tr>
<td>34567</td>
<td>34.57K</td>
</tr>
<tr>
<td>123</td>
<td>123B</td>
</tr>
<tr>
<td>0</td>
<td>Empty</td>
</tr>
<tr>
<td>-10</td>
<td>Replace HDD!</td>
</tr>
</tbody>
</table>

<h3 id="dates-and-time"><a href="#dates-and-time">Dates and Time</a></h3>

<table>
<thead>
<tr>
<th>FORMAT STRING</th>
<th>MEANING</th>
</tr>
</thead>
<tbody>
<tr>
<td>m</td>
<td>Displays the month number without a leading zero.</td>
</tr>
<tr>
<td>mm</td>
<td>Displays the month number with a leading zero.</td>
</tr>
<tr>
<td>mmm</td>
<td>Displays the short month name in the current culture.</td>
</tr>
<tr>
<td>mmmm</td>
<td>Displays the full month name in the current culture.</td>
</tr>
<tr>
<td>d</td>
<td>Displays the date number without a leading zero.</td>
</tr>
<tr>
<td>dd</td>
<td>Displays the date number with a leading zero.</td>
</tr>
<tr>
<td>ddd</td>
<td>Displays the abbreviated weekday name.</td>
</tr>
<tr>
<td>dddd</td>
<td>Displays the full weekday name.</td>
</tr>
<tr>
<td>yy</td>
<td>Displays the year as a two-digit number.</td>
</tr>
<tr>
<td>yyyy</td>
<td>Displays the full year number.</td>
</tr>
<tr>
<td>---------------</td>
<td>-----------------------------------------------------------------------------------</td>
</tr>
<tr>
<td>h</td>
<td>Displays the hour without a leading zero.</td>
</tr>
<tr>
<td>hh</td>
<td>Displays the hour including a leading zero.</td>
</tr>
<tr>
<td>m</td>
<td>Displays the minute without a leading zero.</td>
</tr>
<tr>
<td>mm</td>
<td>Displays the minute including a leading zero.</td>
</tr>
<tr>
<td>s</td>
<td>Displays the second without a leading zero.</td>
</tr>
<tr>
<td>ss</td>
<td>Displays the second including a leading zero.</td>
</tr>
<tr>
<td>[h]</td>
<td>Displays the elapsed time in hours.</td>
</tr>
<tr>
<td>[m]</td>
<td>Displays the elapsed time in minutes.</td>
</tr>
<tr>
<td>[s]</td>
<td>Displays the elapsed time in seconds.</td>
</tr>
<tr>
<td>AM/PM</td>
<td>Displays hours in a 12-hour clock accompanied by an <code>AM</code> or <code>PM</code> indication.</td>
</tr>
<tr>
<td>am/pm</td>
<td>Displays hours in a 12-hour clock accompanied by an <code>am</code> or <code>pm</code> indication.</td>
</tr>
<tr>
<td>A/P</td>
<td>Displays hours in a 12-hour clock accompanied by an <code>A</code> or <code>P</code> indication.</td>
</tr>
<tr>
<td>a/p</td>
<td>Displays hours in a 12-hour clock accompanied by an <code>a</code> or <code>p</code> indication.</td>
</tr>
</tbody>
</table>

<p>Note that the month and minute specifiers are ambiguous (<code>m</code> or <code>mm</code>). These strings are interpreted as a month number, unless preceded by an hour part (<code>h</code> or <code>hh</code>). In such cases, it displays minutes, as demonstrated in the following table.</p>

<table>
<thead>
<tr>
<th>FORMAT STRING</th>
<th>EXAMPEL DISPLAY</th>
</tr>
</thead>
<tbody>
<tr>
<td>d m yyyy</td>
<td>22 9 2015</td>
</tr>
<tr>
<td>h "hours and" m "minutes"</td>
<td>12 hours and 25 minutes</td>
</tr>
</tbody>
</table>

<h2 id="spreadsheet-vs-excel"><a href="#spreadsheet-vs-excel">Spreadsheet vs. Excel</a></h2>

<p>The Spreadsheet does not support the following options that are otherwise available in Excel:</p>

<ul>
<li>Exponent (scientific) notation&amp;mdashh;<code>E+</code>, <code>E-</code> Excel formats.</li>
<li>Filling cell width—<code>*</code> Excel format.</li>
</ul>

<h2 id="see-also"><a href="#see-also">See Also</a></h2>

<p>Other articles on the Kendo UI Spreadsheet:</p>

<ul>
<li><a href="/controls/data-management/spreadsheet/overview">Overview</a></li>
<li><a href="/controls/data-management/spreadsheet/custom-functions">Custom Functions</a></li>
<li><a href="/controls/data-management/spreadsheet/custom-editors">Custom Cell Editors</a></li>
<li><a href="/controls/data-management/spreadsheet/import-and-export-data/json">Store Data as JSON</a></li>
<li><a href="/controls/data-management/spreadsheet/import-and-export-data/export-to-excel">Export to Excel</a></li>
<li><a href="/controls/data-management/spreadsheet/import-and-export-data/server-side-processing">Server-Side Processing</a></li>
<li><a href="/controls/data-management/spreadsheet/end-user/user-guide">User Guide</a></li>
</ul> 
        </article>
    </div>
    <div id="feedback-container">
  <div id="feedback-section">
    <script src="/assets/feedback.js?cb=e99a3802c754fe2425831d58312c7c8b"></script>
    <span id="popupNotification"></span>
    <div id="feedback-menu-container">
      <div id="helpful-buttons-container">
        <span class="side-title">Is this article helpful?</span>
        <a class="button" id="yesButton">Yes</a> /
        <a class="button" id="noButton">No</a>
      </div>
      <div id="feedback-submitted-container">
        <span class="side-title">Thank you for your feedback!</span>
      </div>
    </div>
    <div id="feedback-window-container">
      <div id="feedback-form-window" style="background-color: white;">
        <h1 class="feedback-extw__title">Give article feedback</h1>
        <p>Tell us how we can improve this article</p>
        <span id="feedback-form-popup-container"></span>
        <form id="feedback-form">
          <div id="feedback-checkbox-area">
            <input id="hidden-sheet-id" type="hidden" value="14zzclhdh7dMLpg0iq4-2hweCdEiuxxqP3tHdr42-_Cs">
            <label>
              <input type="checkbox" data-bind="checked: outdatedSample" /> Code samples are inaccurate / outdated.
            </label>
            <span class="required-field" data-bind="visible: outdatedSample"></span>
            <textarea id="feedback-code-sample-text-input" placeholder="Please, specify more details ..." class="feedback-extw__textarea"
              data-bind="visible: outdatedSample, value: inaccurateOutdatedCodeSamplesText">
          </textarea>
            <label>
              <input type="checkbox" data-bind="checked: otherMoreInformation" /> I expected to find other / more information.
            </label>
            <span class="required-field" data-bind="visible: otherMoreInformation"></span>
            <textarea id="feedback-more-information-text-input" class="feedback-extw__textarea" placeholder="Please, specify what information can be added ..."
              data-bind="visible: otherMoreInformation, value: otherMoreInformationText">
          </textarea>
            <label>
              <input type="checkbox" data-bind="checked: textErrors" /> There are typos / broken links / broken page elements.
            </label>
            <span class="required-field" data-bind="visible: textErrors"></span>
            <textarea id="feedback-text-errors-text-input" class="feedback-extw__textarea" placeholder="Please, specify what needs to be fixed ..."
              data-bind="visible: textErrors, value: typosLinksElementsText">
          </textarea>
            <label>
              <input type="checkbox" data-bind="checked: inaccurateContent" /> Content is inaccurate / outdated.
            </label>
            <span class="required-field" data-bind="visible: inaccurateContent"></span>
            <textarea id="feedback-inaccurate-content-text-input" class="feedback-extw__textarea" placeholder="Please, specify which conent ..."
              data-bind="visible: inaccurateContent, value: inaccurateOutdatedContentText"></textarea>
            <label>
              <input type="checkbox" data-bind="checked: otherFeedback" /> Other
            </label>
            <span class="required-field" data-bind="visible: otherFeedback"></span>
            <textarea id="feedback-other-text-input" placeholder="Please, enter more details or not listed feedback ..." class="feedback-extw__textarea"
              data-bind="visible: otherFeedback, value: textFeedback">
          </textarea>
            <input id="feedback-email-input" type="email" placeholder="email (optional)" data-email-msg="Email format is not valid."
              class="feedback-extw__input input" data-bind="value: email" />
          </div>
        </form>
        <div class="feedback-extw__foot">
          <button id="form-submit-button" class="feedback-extw__button button button--action">Send feedback</button>
          <button id="form-close-button" class="feedback-extw__button button">Cancel</button>
        </div>
      </div>
    </div>
    <a id="close-button" class="button">
      <img src="/images/close-btn.svg" alt="close" />
    </a>
  </div>
  <div id="feedback-section-dummy">Dummy</div>
</div>
    <footer>
    <p>Copyright &copy; 2017 Progress Software Corporation and/or its subsidiaries or affiliates. All Rights Reserved.</p>
</footer>

</div>
    </body>
</html>

